﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAL;
using BLL;
using System.Data.SqlClient;

namespace _3tiers
{
    public partial class form_XepLichDay : Form
    {
        public form_XepLichDay()
        {
            InitializeComponent();
        }
        DataAccess ac = new DataAccess();
        BLL.BLL business = new BLL.BLL();
        private void label6_Click(object sender, EventArgs e)
        {

        }

        private void form_XepLichDay_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'phongDataSet.tblPhong' table. You can move, or remove it, as needed.
            this.tblPhongTableAdapter.Fill(this.phongDataSet.tblPhong);
            // TODO: This line of code loads data into the 'hocKyDataSet.tblKi' table. You can move, or remove it, as needed.
            this.tblKiTableAdapter.Fill(this.hocKyDataSet.tblKi);
            // TODO: This line of code loads data into the 'nhomDataSet17.tblNhom' table. You can move, or remove it, as needed.
            this.tblNhomTableAdapter.Fill(this.nhomDataSet17.tblNhom);
            // TODO: This line of code loads data into the 'khoaDataSet.tblKhoa' table. You can move, or remove it, as needed.
            this.tblKhoaTableAdapter.Fill(this.khoaDataSet.tblKhoa);
            ////string sql3 = "select khoa.TenKhoa as Khoa,gv.TenGiaoVien as Ten_Giao_Vien,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblGiaoVien gv,tblNhom nh,tblMon m,tblKhoa khoa where ld.IDGiaoVien=gv.IDGiaoVien and ld.IDMon=m.IDMon and khoa.IDKhoa=gv.IDKhoa and ld.IDNhom=nh.IDNhom";
            //string sql3 = "select khoa.TenKhoa as Khoa,nh.IDNhom as Nhom,m.TenMon,ThuNgay,TietBatDau,TietKetThuc,Phong from tblLichDay ld,tblChiTietLich chitiet,tblNhom nh,tblMon m,tblBoMon bm,tblKhoa khoa where chitiet.IDChiTietLich=ld.IDChiTietLich and m.IDMon=chitiet.IDMon  and chitiet.IDNhom=nh.IDNhom and m.IDBoMon=bm.IDBoMon and bm.IDKhoa=khoa.IDKhoa";
            string sql3 = "stSelectAllLichDayGiaoVien";
            dataGridView1.DataSource = business.ShowData(sql3);
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Hiển thị Bộ Môn khi chọn Khoa
            try
            {
                string IDKhoa = cbKhoa.SelectedValue.ToString();
                string sql = "select * from tblBoMon where IDKhoa='" + IDKhoa + "'";
                DataTable dt = ac.GetTable(sql);
                cbBoMon.DataSource = dt;
                cbBoMon.DisplayMember = "TenBoMon";
                cbBoMon.ValueMember = "IDBoMon";
            }
            catch { };
            //lấy danh sách mã giáo viên ứng với từng bộ môn.
            try
            {
                string idbm = cbBoMon.SelectedValue.ToString();
                string sql = "select * from tblGiaoVien where IDBoMon='" + idbm + "'";
                DataTable dt = ac.GetTable(sql);
                cbMaGV.DataSource = dt;
                cbMaGV.ValueMember = "IDGiaoVien";
                cbMaGV.DisplayMember = "IDGiaoVien";
            }
            catch { };

            //Lấy danh sách MÔN ứng với từng BỘ MÔN
            try
            {
                string idbm = cbBoMon.SelectedValue.ToString();
                string sql1 = "select * from tblMon where IDBoMon='" + idbm + "'";
                DataTable dt1 = ac.GetTable(sql1);
                cbMon.DataSource = dt1;
                cbMon.ValueMember = "IDMon";
                cbMon.DisplayMember = "TenMon";
            }
            catch { };

            try
            {
                string idGiaoVien = cbMaGV.SelectedValue.ToString();
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                tbTenGiaoVien.Text = gv;
            }
            catch
            {
            }
        }
        //chọn combobox bộ môn
        private void cbBoMon_SelectedIndexChanged(object sender, EventArgs e)
        {
            
            //lấy danh sách mã giáo viên ứng với từng bộ môn.
            try
            {
                string idbm = cbBoMon.SelectedValue.ToString();
                string sql = "select * from tblGiaoVien where IDBoMon='" + idbm + "'";
                DataTable dt = ac.GetTable(sql);
                cbMaGV.DataSource = dt;
                cbMaGV.ValueMember = "IDGiaoVien";
                cbMaGV.DisplayMember = "IDGiaoVien";
            }
            catch { }; 

            //Lấy danh sách MÔN ứng với từng BỘ MÔN
            try
            {
                string idbm = cbBoMon.SelectedValue.ToString();
                string sql1 = "select * from tblMon where IDBoMon='" + idbm + "'";
                DataTable dt1 = ac.GetTable(sql1);
                cbMon.DataSource = dt1;
                cbMon.ValueMember = "IDMon";
                cbMon.DisplayMember = "TenMon";
            }
            catch { };            
        }

        private void cbMaGV_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //Hiển thị tên giáo viên ứng với mã giáo viên được chọn
                string idGiaoVien = cbMaGV.SelectedValue.ToString();
                string sql2 = "select TenGiaoVien from tblGiaoVien where IDGiaoVien='" + idGiaoVien + "'";
                string gv = ac.ExcuteScalar(sql2);
                tbTenGiaoVien.Text = gv;
            }
            catch { }
        }

        private void btThem_Click(object sender, EventArgs e)
        {
            string thuNgay = cbThuNgay.Text;
            int tietbatdau = int.Parse(cbTietBatDau.Text);
            int tietketthuc = int.Parse(cbTietKetThuc.Text);
            string namHoc = cbNamHoc.Text;
            string hocKi = cbHocKi.Text;
            string magv = cbMaGV.SelectedValue.ToString();
            string phong = cbPhong.SelectedValue.ToString();
            //string thu = ac.ExcuteScalar("select ThuNgay from tblLichDay where ThuNgay='"+thuNgay+"'");
            //string sql5 = "select ThuNgay from tblLichDay group by(ThuNgay)";
            string sql6 = "select TietBatDau,TietKetThuc from tblLichDay where Phong='"+phong+"' and ThuNgay='"+thuNgay+"' and IDNamHoc='"+namHoc+"' and IDKi='"+hocKi+"'";
            SqlDataReader reader1 = ac.ExecuteReader(sql6);
            while (reader1.Read())
            {
                int tietdau = int.Parse(reader1[0].ToString());
                int tietcuoi = int.Parse(reader1[1].ToString());
                
                if ((tietbatdau >= tietdau && tietbatdau <= tietcuoi) || (tietketthuc >= tietdau && tietketthuc <= tietcuoi))
                {
                    MessageBox.Show("Vui lòng chọn lại thời gian dạy", "Thông báo lỗi");                    
                    goto exit;
                }                
            }

            string sql7 = "select TietBatDau,TietKetThuc from tblLichDay where IDGiaoVien='" + magv + "' and ThuNgay='" + thuNgay + "' and IDNamHoc='" + namHoc + "' and IDKi='" + hocKi + "'";
            SqlDataReader reader2 = ac.ExecuteReader(sql7);
            while (reader2.Read())
            {
                int tietdau = int.Parse(reader2[0].ToString());
                int tietcuoi = int.Parse(reader2[1].ToString());
                //string phonghientai = reader2[2].ToString();

                lbThongBao.Text = Convert.ToString(tietcuoi);
                if ((tietbatdau >= tietdau && tietbatdau <= tietcuoi) || (tietketthuc >= tietdau && tietketthuc <= tietcuoi))
                {
                    MessageBox.Show("Trùng thời gian dạy.Vui lòng chọn lại", "Thông báo lỗi");
                    goto exit;
                }
            }
            
            try
            {
                SqlConnection con = ac.GetConnect();
                con.Open();
                SqlCommand cmd = new SqlCommand("stThemLichDay", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("IDGiaoVien", SqlDbType.NChar).Value = cbMaGV.SelectedValue.ToString();
                cmd.Parameters.Add("@IDMon", SqlDbType.NChar).Value = cbMon.SelectedValue.ToString();
                cmd.Parameters.Add("@IDNhom", SqlDbType.NChar).Value = cbNhom.SelectedValue.ToString();
                cmd.Parameters.Add("@IDKi", SqlDbType.NChar).Value = cbHocKi.SelectedValue.ToString();
                cmd.Parameters.Add("@IDNamHoc", SqlDbType.NChar).Value = cbNamHoc.SelectedValue.ToString();
                cmd.Parameters.Add("@ThuNgay", SqlDbType.NVarChar).Value = cbThuNgay.Text;
                cmd.Parameters.Add("@TietBatDau", SqlDbType.NVarChar).Value = cbTietBatDau.Text;
                cmd.Parameters.Add("@TietKetThuc", SqlDbType.NVarChar).Value = cbTietKetThuc.Text;
                cmd.Parameters.Add("@Phong", SqlDbType.NVarChar).Value = cbPhong.SelectedValue.ToString();                
                cmd.ExecuteNonQuery();
                lbThongBao.Text = "Thêm lịch dạy thành công.";
            }
            catch { lbThongBao.Text = "Thêm lịch dạy thất bại.\n Vui lòng chọn đủ các trường trên"; }            
            
            form_XepLichDay_Load(sender, e);

        exit: ;
        }
        //Nhóm
        private void cbNhom_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        private void cbNamHoc_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                string IDNamHoc = cbNamHoc.SelectedValue.ToString();
                string sql = "select * from tblKi where IDNamHoc='"+IDNamHoc+"'";
                DataTable dt = ac.GetTable(sql);
                cbHocKi.DataSource = dt;
                cbHocKi.ValueMember = "IDKi";
                cbHocKi.DisplayMember = "IDKi";
                
            }
            catch  { }
        }

        private void cbTietBatDau_SelectedIndexChanged(object sender, EventArgs e)
        {
            int bd = int.Parse(cbTietBatDau.SelectedItem.ToString());
            cbTietKetThuc.Items.Clear();
            for (int i = bd + 1; i < 15; i++)
            {
                cbTietKetThuc.Items.Add(i);
            }
            //MessageBox.Show(bd);

        }
    }
}
